package com.sztz.utils;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import com.alibaba.excel.EasyExcelFactory;
import com.sztz.constants.CommonConstants;
import com.sztz.entity.*;
import com.sztz.enums.CustomerBelongEnum;
import lombok.extern.slf4j.Slf4j;

import java.io.File;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

/**
 * 处理企业信息里的客服经理，实施经理之类的
 * @author yzy
 * @date 2022/4/7 11:12
 */
@Slf4j
public class FirmInfosManageUtils {

    /**
     * 读取客服提供的关联企业EXCEL文件，获取企业相关客服经理，实施经理，所属平台信息
     * @return List<InitFirm> 列表数据
     * @author yzy
     * @date 2022/04/07 11:20
     */
    public static List<InitFirm> readInitFirmExcel(){
        return EasyExcelFactory.read(new File(CommonConstants.BASE_EXCEL_PATH+"\\20220407\\init_firm.xlsx")).head(InitFirm.class).sheet().doReadSync();
    }
    /**
     * 读取企业EXCEL文件，获取企业相关客服经理，实施经理，所属平台信息
     * @return List<FirmInfo> 列表数据
     * @author yzy
     * @date 2022/04/07 11:20
     */
    public static List<FirmInfo> readFirmInfoExcel(){
        return EasyExcelFactory.read(new File(CommonConstants.BASE_EXCEL_PATH+"\\20220407\\firm_info.xls")).head(FirmInfo.class).sheet().doReadSync();
    }
    /**
     * 读取企业EXCEL文件，获取企业相关客服经理，实施经理，所属平台信息
     * @return List<FirmInfo> 列表数据
     * @author yzy
     * @date 2022/04/07 11:20
     */
    public static List<SalesMan> readSalesManExcel(){
        return EasyExcelFactory.read(new File(CommonConstants.BASE_EXCEL_PATH+"\\20220407\\sales_man.xls")).head(SalesMan.class).sheet().doReadSync();
    }
    /**
     * 读取企业EXCEL文件，获取企业相关客服经理，实施经理，所属平台信息
     * @return List<FirmInfo> 列表数据
     * @author yzy
     * @date 2022/04/07 11:20
     */
    public static List<FirmUserInfo> readUserInfoExcel(){
        return EasyExcelFactory.read(new File(CommonConstants.BASE_EXCEL_PATH+"\\20220407\\firm_userinfo.xls")).head(FirmUserInfo.class).sheet().doReadSync();
    }

    /**
     * 组装修改企业信息的SQL脚本
     * @param firmId
     * @param belong
     * @param cssId
     * @param cssName
     * @param impId
     * @param impName
     * @return
     */
    public static String createFirmInfoSql(String firmId,int belong,String cssId,String cssName,String impId,String impName){
        String sql = "UPDATE TZ_USERCENTER.FIRM_INFOS SET customer_belong={},customer_service_staff_id='{}',customer_service_staff_name='{}',implementer_id='{}',,implementer_name='{}' WHERE ID='{}';";
        return StrUtil.format(sql,belong,cssId,cssName,impId,impName,firmId);
    }
    /**
     * 组装修改平台统计数据的SQL脚本
     * @param firmId
     * @param smId
     * @param sdId
     * @param cssId
     * @param impId
     * @param belong
     * @return String sql
     */
    public static String createFirmBillStatisticsSql(String firmId,String smId,String sdId,String cssId,String impId,int belong,String createDate){
        String sql = "UPDATE TZ_CLOUD_GUARD.FIRM_BILL_STATISTICS SET SALES_MANAGE_ID='{}',SALES_DEPT_ID='{}',CUSTOMER_SERVICE_STAFF_ID='{}',IMPLEMENTER_ID='{}',CUSTOMER_BELONG={},firm_create_time='{}' WHERE FIRM_ID='{}';";
        return StrUtil.format(sql,smId,sdId,cssId,impId,belong,createDate,firmId);
    }
    /**
     * 组装修改销售经理的SQL脚本
     * @param customerId
     * @param samId
     * @param samName
     * @return
     */
    public static String createUpdateSalesManSql(String customerId,String samId,String samName){
        String sql = "UPDATE TZ_USERCENTER.firm_customer_salesman SET salesman_id='{}',salesman='{}' WHERE customer_id='{}';";
        return StrUtil.format(sql,samId,samName,customerId);
    }
    /**
     * 组装插入销售经理的SQL脚本
     * @param customerId
     * @param samId
     * @param samName
     * @return
     */
    public static String createInsertSalesManSql(String customerId,String samId,String samName){
        String sql = "INSET INTO TZ_USERCENTER.firm_customer_salesman(customer_id,salesman_id,salesman) VALUES('{}','{}','{}');";
        return StrUtil.format(sql,samId,samName,customerId);
    }

    /**
     * 修改计费统计数据的SQL脚本
     * @param samId
     * @param sdId
     * @param cssId
     * @param impId
     * @param belong
     * @param createDate
     * @param regNo
     * @return
     */
    public static String createUpdateChargeStatisticsSQL(String samId,String sdId, String cssId,String impId,int belong,String createDate,String regNo){
        String sql="UPDATE TZ_CHARGE.CHARGE_STATISTICS SET sales_manage_id='{}', sales_dept_id='{}',customer_service_staff_id='{}', implementer_id='{}',customer_belong= {}, customer_firm_create_date='{}' WHERE cus_credit_code='{}';";
        return StrUtil.format(sql,samId,sdId,cssId,impId,belong,createDate,regNo);
    }
    /**
     * 修改计费统计数据的SQL脚本
     * @param samId
     * @param cssId
     * @param impId
     * @param belong
     * @param createDate
     * @param firmId
     * @return
     */
    public static String createUpdateBillSQL(String samId,String cssId,String impId,int belong,String createDate,String firmId){
        String sql="UPDATE TZ_CHARGE.BILL_MAIN SET salesman_id='{}',customer_service_staff_id='{}', implementer_id='{}',customer_belong= {}, customer_firm_create_date='{}' WHERE dedu_fee_compy_id='{}';";
        return StrUtil.format(sql,samId,cssId,impId,belong,createDate,firmId);
    }
    /**
     * 输出企业信息修改SQL到文件中
     * @author yzy
     * @date 2022/04/07 11:20
     */
    public static void outputFirmInfoSql(List<String> collect){
        String outFilePath= StrUtil.concat(Boolean.TRUE,CommonConstants.BASE_EXCEL_PATH,"\\20220407\\out\\firmInfo.sql");
        FileUtil.appendUtf8Lines(collect,new File(outFilePath));
    }
    /**
     * 输出销售经理信息修改SQL到文件中
     * @author yzy
     * @date 2022/04/07 11:20
     */
    public static void outputCustomerInfoSalesmanSql(List<String> collect){
        String outFilePath= StrUtil.concat(Boolean.TRUE,CommonConstants.BASE_EXCEL_PATH,"\\20220407\\out\\salesmanInfo.sql");
        FileUtil.appendUtf8Lines(collect,new File(outFilePath));
    }
    /**
     * 输出平台单证统计信息修改SQL到文件中
     * @author yzy
     * @date 2022/04/07 11:20
     */
    public static void outputFirmBillStatisticsSql(List<String> collect){
        String outFilePath= StrUtil.concat(Boolean.TRUE,CommonConstants.BASE_EXCEL_PATH,"\\20220407\\out\\firmBillStatistics.sql");
        FileUtil.appendUtf8Lines(collect,new File(outFilePath));
    }
    /**
     * 输出计费统计信息修改SQL到文件中
     * @author yzy
     * @date 2022/04/08 16:00
     */
    public static void outputChargeStatisticsSql(List<String> collect){
        String outFilePath= StrUtil.concat(Boolean.TRUE,CommonConstants.BASE_EXCEL_PATH,"\\20220407\\out\\chargeStatistics.sql");
        FileUtil.appendUtf8Lines(collect,new File(outFilePath));
    }
    /**
     * 输出应收账款统计信息修改SQL到文件中
     * @author yzy
     * @date 2022/04/08 16:00
     */
    public static void outputBillMainSql(List<String> collect){
        String outFilePath= StrUtil.concat(Boolean.TRUE,CommonConstants.BASE_EXCEL_PATH,"\\20220407\\out\\billMain.sql");
        FileUtil.appendUtf8Lines(collect,new File(outFilePath));
    }


    public static void main(String[] args) {
        processData();
    }

    public static void processData(){
        List<InitFirm> initFirmList =  readInitFirmExcel();
        List<FirmInfo> firmInfoList = readFirmInfoExcel();
        List<SalesMan> salesManList = readSalesManExcel();
        List<FirmUserInfo> userInfoList = readUserInfoExcel();
        //企业信息SQL
        List<String> firmInfoSqlList = new ArrayList<>();
        //销售经理SQL
        List<String> salesmanSqlList = new ArrayList<>();
        //统计数据SQL
        List<String> firmBillStatisticsSqlList = new ArrayList<>();
        initFirmList.forEach(item->{
            Optional<FirmInfo> first = firmInfoList.stream().filter(firmInfo -> StrUtil.equalsAnyIgnoreCase(item.getId(), firmInfo.getId())
                    &&  StrUtil.equalsAnyIgnoreCase(item.getRegisteredNo(), firmInfo.getRegisteredNo())).findFirst();
            if(first.isPresent()){
                FirmInfo firmInfo = first.get();
                int belong = 0;
                //获取所属客户信息
                CustomerBelongEnum enumByName = CustomerBelongEnum.getEnumByName(StrUtil.trim(item.getCustomerBelong()));
                if(ObjectUtil.isNull(enumByName)){
                    log.info("无法获取所属客户类型:{}",JSONUtil.toJsonStr(item));
                    return ;
                }else{
                    belong = enumByName.getCode();
                }
                //获取客服经理
                FirmUserInfo cssUserInfo = getFirmUserInfo(userInfoList, item, 0);
                //获取实施经理
                FirmUserInfo impUserInfo = getFirmUserInfo(userInfoList, item,1);
                String cssId= ObjectUtil.isNull(cssUserInfo)?StrUtil.EMPTY:cssUserInfo.getId();
                String cssName= ObjectUtil.isNull(cssUserInfo)?StrUtil.EMPTY:cssUserInfo.getFullName();
                String impId= ObjectUtil.isNull(impUserInfo)?StrUtil.EMPTY:impUserInfo.getId();
                String impName= ObjectUtil.isNull(impUserInfo)?StrUtil.EMPTY:impUserInfo.getFullName();
                firmInfoSqlList.add(createFirmInfoSql(firmInfo.getId(),belong,cssId,cssName,impId,impName));

                //获取销售经理
                if(StrUtil.isNotBlank(item.getSalesmanName())){
                    Optional<FirmUserInfo> findUserInfo = userInfoList.stream().filter(userInfo -> StrUtil.equalsAnyIgnoreCase(userInfo.getFullName(), item.getSalesmanName())).findFirst();
                    if(findUserInfo.isPresent()){
                        //获取到配置的销售经理
                        FirmUserInfo salesUserInfo = findUserInfo.get();
                        firmBillStatisticsSqlList.add(createFirmBillStatisticsSql(firmInfo.getId(),salesUserInfo.getId(),salesUserInfo.getOrgId(),cssId,impId,belong,firmInfo.getCreateDate()));
                        Optional<SalesMan> findSalesMan = salesManList.stream().filter(salesMan -> StrUtil.equalsAnyIgnoreCase(salesMan.getCustomerId(), firmInfo.getFirmCustomerId())).findFirst();
                        if(findSalesMan.isPresent()){
                            salesmanSqlList.add(createUpdateSalesManSql(firmInfo.getFirmCustomerId(),salesUserInfo.getId(),salesUserInfo.getFullName()));
                        }else{
                            log.info("需要新增销售经理");
                            salesmanSqlList.add(createInsertSalesManSql(firmInfo.getFirmCustomerId(),salesUserInfo.getId(),salesUserInfo.getFullName()));
                        }
                    }else{
                        log.info("无法获取销售经理:{}",JSONUtil.toJsonStr(item));
                    }
                }
            }else{
                log.info("企业id与信用代码不匹配",JSONUtil.toJsonStr(item));
            }
        });
        //outputFirmInfoSql(firmInfoSqlList);
        //outputCustomerInfoSalesmanSql(salesmanSqlList);
        outputFirmBillStatisticsSql(firmBillStatisticsSqlList);
    }

    public static void processChargeData(){
        //客服提供的企业相关经理信息
        List<InitFirm> initFirmList =  readInitFirmExcel();
        //数据库企业信息
        List<FirmInfo> firmInfoList = readFirmInfoExcel();
        //泰洲科技所有用户信息
        List<FirmUserInfo> userInfoList = readUserInfoExcel();
        //计费统计SQL
        List<String> chargeStatisticsSqlList = new ArrayList<>();
        //账单SQL
        List<String> billMainSqlList = new ArrayList<>();
        initFirmList.forEach(item->{
            Optional<FirmInfo> first = firmInfoList.stream().filter(firmInfo -> StrUtil.equalsAnyIgnoreCase(item.getId(), firmInfo.getId())
                    &&  StrUtil.equalsAnyIgnoreCase(item.getRegisteredNo(), firmInfo.getRegisteredNo())).findFirst();
            if(first.isPresent()){
                FirmInfo firmInfo = first.get();
                int belong = 0;
                //获取所属客户信息
                CustomerBelongEnum enumByName = CustomerBelongEnum.getEnumByName(StrUtil.trim(item.getCustomerBelong()));
                if(ObjectUtil.isNull(enumByName)){
                    log.info("无法获取所属客户类型:{}",JSONUtil.toJsonStr(item));
                    return ;
                }else{
                    belong = enumByName.getCode();
                }
                //获取客服经理
                FirmUserInfo cssUserInfo = getFirmUserInfo(userInfoList, item, 0);
                //获取实施经理
                FirmUserInfo impUserInfo = getFirmUserInfo(userInfoList, item,1);
                //获取销售经理
                FirmUserInfo salesUserInfo = getFirmUserInfo(userInfoList, item,2);
                String cssId= ObjectUtil.isNull(cssUserInfo)?StrUtil.EMPTY:cssUserInfo.getId();
                String impId= ObjectUtil.isNull(impUserInfo)?StrUtil.EMPTY:impUserInfo.getId();
                String salesId = ObjectUtil.isNull(salesUserInfo)?StrUtil.EMPTY:salesUserInfo.getId();
                String salesOrgId = ObjectUtil.isNull(salesUserInfo)?StrUtil.EMPTY:salesUserInfo.getOrgId();
                chargeStatisticsSqlList.add(createUpdateChargeStatisticsSQL(salesId,salesOrgId,cssId,impId,belong,firmInfo.getCreateDate(),firmInfo.getRegisteredNo()));
                //billMainSqlList.add(createUpdateBillSQL(salesId,cssId,impId,belong,firmInfo.getCreateDate(),firmInfo.getFirmCustomerId()));
            }else{
                log.info("企业id与信用代码不匹配",JSONUtil.toJsonStr(item));
            }
        });
        outputChargeStatisticsSql(chargeStatisticsSqlList);
        //outputBillMainSql(billMainSqlList);
    }


    /**
     * 获取相关经理信息
     * @param userInfoList
     * @param item
     * @param type 0 客服经理 1 实施经理 2 销售经理
     * @return
     */
    private static FirmUserInfo getFirmUserInfo(List<FirmUserInfo> userInfoList, InitFirm item, int type) {
        FirmUserInfo firmUserInfo = null;
        String manageName = type==0?item.getCustomerServiceStaffName():type==1?item.getImplementerName():item.getSalesmanName();
        //获取经理
        if(StrUtil.isNotBlank(manageName)){
            Optional<FirmUserInfo> findUserInfo = userInfoList.stream().filter(userInfo -> StrUtil.equalsAnyIgnoreCase(userInfo.getFullName(), manageName)).findFirst();
            if(findUserInfo.isPresent()){
                firmUserInfo = findUserInfo.get();
            }else{
                log.info("无法获取{}经理:{}",type==0?"客服":type==1?"实施":"销售", JSONUtil.toJsonStr(item));
            }
        }
        return firmUserInfo;
    }

}
